package biz.bbtec.sbm.issuetracking.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import com.opensymphony.xwork2.ActionContext;

import biz.bbtec.sbm.db.dbConnection;
import biz.bbtec.sbm.issuetracking.bean.AttachmentBean;
import biz.bbtec.sbm.issuetracking.bean.NoteBean;
import biz.bbtec.sbm.issuetracking.bean.WorkBean;
import biz.bbtec.sbm.user.bean.UserBean;

public class NoteService {
	dbConnection db = new dbConnection();

	public boolean ModifyNote(NoteBean note) {
		boolean flag = false;
		String sql = "update note set title=?, text=? where noteid= " + "'"
				+ note.getNoteid() + "'";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		try {
			past = conn.prepareStatement(sql);
			past.setString(1, note.getTitle());
			past.setString(2, note.getText());
			past.executeUpdate();
			past.close();
			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public boolean DeleteNote(NoteBean note) throws SQLException {
		boolean flag = false;

		Connection conn = db.getConnection();
		String sql1 = "delete from note where noteid =" + "'"
				+ note.getNoteid() + "'";
		try {
			PreparedStatement past = null;
			past = conn.prepareStatement(sql1);
			past.executeUpdate(sql1);
			flag = true;
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public boolean AddNote(NoteBean notebean, String filename)
			throws ParseException, SQLException {

		boolean flag = false;
		int wpid = 0;
		Map session = ActionContext.getContext().getSession();
		if (session.get("wpid") != null) {
			wpid = (Integer) session.get("wpid");
		} else {
			if (session.get("addnotewpid") != null) {
				wpid = (Integer) session.get("addnotewpid");
			}
		}
		int taskid = (Integer) session.get("worktaskid");
		UserBean user = (UserBean) session.get("user");

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		String thisDate = sdf.format(date);

		Connection conn = db.getConnection();
		PreparedStatement past = null;

		String sql1 = "insert into note(title,text,userid,wpid,taskid,date) values("
				+ "'"
				+ notebean.getTitle()
				+ "'"
				+ ","
				+ "'"
				+ notebean.getText()
				+ "'"
				+ ","
				+ "'"
				+ user.getUserid()
				+ "'"
				+ ","
				+ "'"
				+ wpid
				+ "'"
				+ ","
				+ "'"
				+ taskid
				+ "'"
				+ ","
				+ "'"
				+ thisDate
				+ "'"
				+ ")";

		int noteid = 0;
		try {
			past = conn.prepareStatement(sql1, Statement.RETURN_GENERATED_KEYS);
			past.executeUpdate(sql1);
			ResultSet rs = past.getGeneratedKeys();
			if (rs.next()) {
				noteid = rs.getInt(1);
			} else {
				return flag;
			}
			past.close();
			flag = true;

			if (filename != null) {
				String sql2 = "insert into attachment(noteid,title,date,wpid,taskid) values ("
						+ "'"
						+ noteid
						+ "'"
						+ ","
						+ "'"
						+ filename
						+ "'"
						+ ","
						+ "'"
						+ thisDate
						+ "'"
						+ ","
						+ "'"
						+ wpid
						+ "'"
						+ ","
						+ "'" + taskid + "'" + ")";
				past = conn.prepareStatement(sql2);
				past.executeUpdate(sql2);
				flag = true;
			}
			past.close();
			rs.close();

			String sql3 = "select user.userid from user,taskaccess,note where note.taskid = taskaccess.taskid and taskaccess.userid=user.userid and note.noteid="
					+ "'" + noteid + "'";
			int judge = 1;
			try {
				past = conn.prepareStatement(sql3);
				rs = past.executeQuery();
				PreparedStatement past2 = null;
				while (rs.next()) {
					flag = false;
					int userid = Integer.parseInt(rs.getString("userid"));
					String sql4 = "insert into noteaccess (noteid,userid,past) values ("
							+ "'"
							+ noteid
							+ "'"
							+ ","
							+ "'"
							+ userid
							+ "'"
							+ "," + "'" + judge + "'" + ")";
					try {
						past2 = conn.prepareStatement(sql4);
						past2.executeUpdate(sql4);
					} catch (SQLException e) {
						e.printStackTrace();
					} finally {
						flag = true;
						past2.close();
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				past.close();
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public List ShowAllNote(WorkBean work, int pagenow, int pagesize) {
		boolean flag = false;
		int notewpid = 0;
		if (work == null) {
			work = new WorkBean();
			Map session = ActionContext.getContext().getSession();
			if (session.get("wpid") != null) {
				notewpid = (Integer) session.get("wpid");
			} else {
				if (session.get("addnotewpid") != null) {
					notewpid = (Integer) session.get("addnotewpid");
				}
			}
			work.setWpid(notewpid);
			session.remove("wpid");
		} else {
			notewpid = work.getWpid();
		}
		Map session = ActionContext.getContext().getSession();
		UserBean user = (UserBean) session.get("user");
		List<NoteBean> list = new ArrayList();
		String sql = "select * from note,user where user.userid = note.userid and note.wpid = "
				+ "'" + notewpid + "'" + " order by date desc limit ?,?";
		String sql3 = "select noteid from noteaccess";
		String sql5 = "update workaccess set past =? where wpid = " + "'"
				+ notewpid + "'" + " and userid = " + "'" + user.getUserid()
				+ "'";
		Connection conn = db.getConnection();
		try {
			PreparedStatement past = conn.prepareStatement(sql);
			past.setInt(1, pagenow * pagesize);
			past.setInt(2, pagesize);
			ResultSet rs = past.executeQuery();
			past = conn.prepareStatement(sql5);
			past.setInt(1, 0);
			past.executeUpdate();
			past.close();
			while (rs.next()) {
				NoteBean note = new NoteBean();
				note.setNoteid(Integer.parseInt(rs.getString("noteid")));
				int noteid = note.getNoteid();
				String sql2 = "select count(*) from attachment where noteid ="
						+ "'" + noteid + "'" + "group by noteid";
				try {
					Statement stmt = conn.createStatement();
					ResultSet rs2 = stmt.executeQuery(sql2);
					if (rs2.next()) {
						int attno = rs2.getInt(1);
						note.setAttno(attno);
					}

				} catch (SQLException e) {
					e.printStackTrace();
				}
				note.setUserid(Integer.parseInt(rs.getString("userid")));
				note.setWpid(work.getWpid());
				note.setTaskid(Integer.parseInt(rs.getString("taskid")));
				note.setTitle(rs.getString("title"));

				String content = rs.getString("text");
				content = content.replace(" ", "&nbsp;");
				content = content.replace("\n", "<br />");
				note.setText(content);

				note.setName(rs.getString("name"));
				note.setDate(rs.getString("date"));
				try {
					PreparedStatement past2 = conn.prepareStatement(sql3);
					ResultSet rs1 = past2.executeQuery();
					while (rs1.next()) {
						int id = Integer.parseInt(rs.getString("noteid"));
						if (id == noteid) {
							flag = true;
							break;
						}
					}
					rs1.close();
					past2.close();
					PreparedStatement past3 = null;
					ResultSet rs3 = null;
					if (flag) {
						String sql4 = "select past from noteaccess where noteid = "
								+ "'"
								+ noteid
								+ "'"
								+ " and userid = "
								+ "'"
								+ user.getUserid() + "'";
						past3 = conn.prepareStatement(sql4);
						rs3 = past3.executeQuery();
						if (rs3.next()) {
							note.setPast(Integer.parseInt(rs3.getString("past")));
							rs3.close();
							past3.close();
						}
					} else {
						note.setPast(0);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				list.add(note);
			}
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}

	public int CountAllNote(WorkBean work) {
		int total = 0;
		int notewpid = 0;
		if (work == null) {
			work = new WorkBean();
			Map session = ActionContext.getContext().getSession();
			if (session.get("wpid") != null) {
				notewpid = (Integer) session.get("wpid");
			} else {
				if (session.get("addnotewpid") != null) {
					notewpid = (Integer) session.get("addnotewpid");
				}
			}
			work.setWpid(notewpid);
			session.remove("wpid");
		} else {
			notewpid = work.getWpid();
		}
		String sql1 = "select count(*) from note where note.wpid = " + "'"
				+ notewpid + "'";

		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;
		try {
			past = conn.prepareStatement(sql1);

			rs = past.executeQuery();
			if (rs.next()) {
				total = rs.getInt(1);
			}
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return total;
	}

	public NoteBean ShowOneNote(NoteBean notebean) throws SQLException {
		NoteBean note = new NoteBean();
		String sql = "select * from note where noteid = ? ";
		int judge = 0;
		Map session = ActionContext.getContext().getSession();
		UserBean user = (UserBean) session.get("user");
		String sql2 = "update noteaccess set past = ? where noteid = " + "'"
				+ notebean.getNoteid() + "'" + " and userid = " + "'"
				+ user.getUserid() + "'";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		try {
			past = conn.prepareStatement(sql);
			past.setInt(1, notebean.getNoteid());
			ResultSet rs = past.executeQuery();
			if (!rs.next()) {
				return null;
			}
			note.setNoteid(Integer.parseInt(rs.getString("noteid")));
			note.setTitle(rs.getString("title"));
			note.setText(rs.getString("text"));
			rs.close();
			past.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		try {
			past = conn.prepareStatement(sql2);
			past.setInt(1, judge);
			past.executeUpdate();
			past.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return note;
	}
	public String showTaskName(int taskid) {
		String name = new String();
		String sql1 = "select name from task where taskid = ?";
		Connection conn = db.getConnection();
		PreparedStatement ps1 = null;
		ResultSet rs1 = null;
		try {
			ps1 = conn.prepareStatement(sql1);
			ps1.setInt(1, taskid);
			rs1 = ps1.executeQuery();
			if (rs1.next()) {
				name = rs1.getString(1);
			} else {
				name = null;
			}
			rs1.close();
			ps1.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return name;
	}

	public List<AttachmentBean> attlist(NoteBean note) {
		List<AttachmentBean> list = new ArrayList<AttachmentBean>();
		String sql1 = "select attid,title from attachment where noteid = ?";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;
		try {
			past = conn.prepareStatement(sql1);
			past.setInt(1, note.getNoteid());
			rs = past.executeQuery();
			while (rs.next()) {
				AttachmentBean att = new AttachmentBean();
				att.setAttid(rs.getInt(1));
				att.setTitle(rs.getString(2));
				att.setNoteid(note.getNoteid());
				list.add(att);
			}
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}

	public String showwptitle(WorkBean work) {

		int total = 0;
		int notewpid = 0;
		if (work == null) {
			work = new WorkBean();
			Map session = ActionContext.getContext().getSession();
			if (session.get("wpid") != null) {
				notewpid = (Integer) session.get("wpid");
			} else {
				if (session.get("addnotewpid") != null) {
					notewpid = (Integer) session.get("addnotewpid");
				}
			}
			work.setWpid(notewpid);
			session.remove("wpid");
		} else {
			notewpid = work.getWpid();
		}

		String wptitle = new String();

		String sql1 = "select title from workpackage where wpid = ?";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;

		try {
			past = conn.prepareStatement(sql1);
			past.setInt(1, work.getWpid());
			rs = past.executeQuery();
			if (rs.next()) {
				wptitle = rs.getString(1);
			}
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}

		return wptitle;
	}
}
